CREATE TABLE [dbo].[System_Params]
(
[ParameterName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_System_Params_ParameterName] DEFAULT (''),
[ShortValue] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_System_Params_ShortValue] DEFAULT (''),
[LongValue] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TIME_STAMP] [timestamp] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
CREATE TRIGGER asi_System_Params_InsertUpdate
   ON  [dbo].[System_Params]
   AFTER INSERT,UPDATE
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    IF EXISTS (SELECT 1 FROM inserted WHERE [ParameterName] = 'System_Control.LicensedProducts')
    BEGIN
        -- script to parse licensed products string
        DECLARE @products varchar(4000)
        DECLARE @licenseKey uniqueidentifier

        TRUNCATE TABLE [dbo].[LicenseLegacyList]

        SELECT @products =  CASE WHEN DATALENGTH(ShortValue) > 0 THEN ShortValue ELSE LongValue END
          FROM [dbo].[System_Params]
         WHERE [ParameterName] = 'System_Control.LicensedProducts'

        -- assume comma delimiter for all products, and eliminate the special case of the last license key
        SET @products = REPLACE(@products, '/', ',')  -- just in case; there may be some slash-delimited CS licenses
        SET @products = @products + ','

        DECLARE @token varchar(50)
        WHILE (DATALENGTH (@products) > 0)
        BEGIN
            SET @token = SUBSTRING (@products, 1, CHARINDEX (',', @products) - 1)
            SET @products = SUBSTRING (@products, CHARINDEX (',', @products) + 1, 4000)

            IF (DATALENGTH(@token) > 0 and @token <> ',')
            BEGIN
                IF (CHARINDEX ('ECMADV', @token) <> 0) BEGIN SET @token = 'ECMADV' END
                IF (CHARINDEX ('ECMSIM', @token) <> 0) BEGIN SET @token = 'ECMSIM' END
                IF (CHARINDEX ('CASUAL', @token) <> 0) BEGIN SET @token = 'CASUAL' END
                IF (CHARINDEX ('ACCU',   @token) <> 0) BEGIN SET @token = 'ACCU'   END
                IF (CHARINDEX ('PROS',   @token) <> 0) BEGIN SET @token = 'PROS'   END
                IF (CHARINDEX ('CERTIFICATION', @token) <> 0) BEGIN SET @token = 'CERT' END
                IF (CHARINDEX ('RELATIONSHIPS', @token) <> 0) BEGIN SET @token = 'RELATION' END

                SET @licenseKey = NULL
                SELECT @licenseKey = LicenseKey FROM [dbo].[LicenseLegacyMap] WHERE [LegacyLicenseCode] = @token

                IF NOT EXISTS (SELECT 1 FROM [dbo].[LicenseLegacyList] WHERE [LegacyLicenseCode] = @token)
                BEGIN
                    INSERT INTO [dbo].[LicenseLegacyList] ([LicenseLegacyKey], [LegacyLicenseCode])
                    VALUES (ISNULL(@licenseKey, NEWID()), @token)
                END
            END
        END
    END
END

GO
ALTER TABLE [dbo].[System_Params] ADD CONSTRAINT [PK_System_Params] PRIMARY KEY CLUSTERED ([ParameterName]) ON [PRIMARY]
GO
GRANT REFERENCES ON  [dbo].[System_Params] TO [ASIGOPHER]
GRANT SELECT ON  [dbo].[System_Params] TO [ASIGOPHER]
GRANT INSERT ON  [dbo].[System_Params] TO [ASIGOPHER]
GRANT DELETE ON  [dbo].[System_Params] TO [ASIGOPHER]
GRANT UPDATE ON  [dbo].[System_Params] TO [ASIGOPHER]
GRANT REFERENCES ON  [dbo].[System_Params] TO [IMIS]
GRANT SELECT ON  [dbo].[System_Params] TO [IMIS]
GRANT INSERT ON  [dbo].[System_Params] TO [IMIS]
GRANT DELETE ON  [dbo].[System_Params] TO [IMIS]
GRANT UPDATE ON  [dbo].[System_Params] TO [IMIS]
GO